#include "mainwindow.h"
#include "ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    //初始化
    ui->stackedWidget->setCurrentIndex(0);

    //数据库引擎
    foreach(QString i ,QSqlDatabase::drivers())
    {
        ui->textBrowser->append(i);
    }
}

MainWindow::~MainWindow()
{
    delete ui;
}


// 第二页
void MainWindow::on_pb_next_clicked()
{
    ui->stackedWidget->setCurrentIndex(1);

    //do something
}
// 主页
void MainWindow::on_pb_back_clicked()
{
    ui->stackedWidget->setCurrentIndex(0);

    //do something
}

// 打开数据库
void MainWindow::on_pushButton_clicked()
{
    db = QSqlDatabase::addDatabase("QSQLITE","kali");
    db.setDatabaseName("C:\\aaa\\mysqlite.db");
    db.setHostName("localhost");
    db.setUserName("username");
    db.setPassword("password");
    ui->textBrowser_2->append("数据库的相关信息如下：");
    ui->textBrowser_2->append("db.port = " + QString::number(db.port()));
    ui->textBrowser_2->append("db.hostName = " + db.hostName());
    ui->textBrowser_2->append("db.password = " + db.password());
    ui->textBrowser_2->append("db.userName = " + db.userName());
    ui->textBrowser_2->append("db.driverName = " + db.driverName());
    ui->textBrowser_2->append("db.databaseName = " + db.databaseName());
    ui->textBrowser_2->append("db.connectOptions = " + db.connectOptions());
    ui->textBrowser_2->append("db.connectionName = " + db.connectionName());
    if (db.isValid()) // 数据库存在
    {
        if (db.open())
        {
            QMessageBox::information(NULL,"打开数据库","数据库"+db.databaseName()+"打开成功!  数据库引擎： "+db.driverName(),QMessageBox::Close);
        }
        else {
            QMessageBox::critical(NULL,"打开数据库","数据库"+db.databaseName()+"打开失败："+db.lastError().text(),QMessageBox::Close);
            return;
        }
    }
}
// 数据库删除
void MainWindow::on_pushButton_5_clicked()
{
    //db.removeDatabase("kali");
    // 从应用程序中删除数据库对象
    QSqlDatabase::removeDatabase(db.connectionName());

    // 删除数据库文件
    QFile::remove(db.databaseName());
}
// 关闭数据库
void MainWindow::on_pushButton_7_clicked()
{
    db.close();
}

// 创建表
void MainWindow::on_pushButton_2_clicked()
{
    ui->tableWidget->setRowCount(1000);
    ui->tableWidget->setColumnCount(5);
    ui->tableWidget->setHorizontalHeaderLabels(QStringList()<<"id"<<"name"<<"age"<<"qq"<<"phone");
    ui->tableWidget->resizeRowsToContents();
    ui->tableWidget->resizeColumnsToContents();
    ui->tableWidget->setColumnWidth(0,50);
    ui->tableWidget->setColumnWidth(1,60);
    ui->tableWidget->setColumnWidth(2,50);
    ui->tableWidget->setColumnWidth(3,100);
    ui->tableWidget->setColumnWidth(4,100);
    ui->tableWidget->setStyleSheet("QTableView {border:2px,solid blue;}");

    QSqlQuery db_query(db);
    ui->textBrowser_2->append("\r\n查询类的相关信息如下：");
    ui->textBrowser_2->append("db_query.size = " + QString::number(db_query.size()));
    ui->textBrowser_2->append("db_query.lastQuery = " + db_query.lastQuery());
    ui->textBrowser_2->append("db_query.lastInsertId = " + db_query.lastInsertId().toString());
    ui->textBrowser_2->append("db_query.executedQuery = " + db_query.executedQuery());

    if (db_query.exec("CREATE TABLE student (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,age INTEGER NOT NULL check(age > 0), qq CHAR(15) NOT NULL UNIQUE, phone INTEGER DEFAULT  10086)")){
        QMessageBox::information(NULL,"创建表","创建成功:"+db_query.lastQuery(),QMessageBox::Close);
    }
    else{
        QMessageBox::critical(NULL,"创建表","表创建失败：" + db_query.lastError().text(),QMessageBox::Close);
    }
}

// 表格插入
void MainWindow::on_pushButton_3_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec(QString("INSERT INTO student (id,name,age,qq,phone) VALUES (%1,'%2',%3,'%4',%5)").arg(ui->lineEdit_id->text()).arg(ui->lineEdit_name->text()).arg(ui->lineEdit_age->text()).arg(ui->lineEdit_qq->text()).arg(ui->lineEdit_phone->text()))){
        QMessageBox::information(NULL,"插入表","插入成功:"+db_query.lastQuery(),QMessageBox::Close);
    }
    else{
        QMessageBox::critical(NULL,"插入表","插入失败:" +db_query.lastError().text() ,QMessageBox::Close);
        return;
    }
}
// 表格查询 升序
void MainWindow::on_pushButton_4_clicked()
{
    QSqlQuery db_query(db);
    int row = 0;
    if (db_query.exec("SELECT id,name,age,qq,phone FROM student  ORDER BY id ASC"))
    {
        QMessageBox::information(NULL,"查询表","查询成功:"+db_query.lastQuery(),QMessageBox::Close);

        while (db_query.next()) {
            int id = db_query.value(0).toInt();
            QString name = db_query.value(1).toString();
            int age = db_query.value(2).toInt();
            QString qq = db_query.value(3).toString();
            quint64 phone = db_query.value(4).toULongLong();
            ui->tableWidget->setItem(row,0,new QTableWidgetItem(QString::number(id)));
            ui->tableWidget->setItem(row,1,new QTableWidgetItem(name));
            ui->tableWidget->setItem(row,2,new QTableWidgetItem(QString::number(age)));
            ui->tableWidget->setItem(row,3,new QTableWidgetItem(qq));
            ui->tableWidget->setItem(row,4,new QTableWidgetItem(QString::number(phone)));
            row += 1;   //行数动态增加
        }
    }
    else {
        QMessageBox::critical(NULL,"查询表","查询失败:" +db_query.lastError().text() ,QMessageBox::Close);
        return;
    }
}

// 删除表
void MainWindow::on_pushButton_6_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("DROP TABLE IF EXISTS student")){
        QMessageBox::information(NULL,"删除表","删除成功+命令："+db_query.lastQuery(),QMessageBox::Close);
        ui->tableWidget->clearContents();
    }
    else{
        QMessageBox::critical(NULL,"插入表","插入失败:" +db_query.lastError().text() ,QMessageBox::Close);
        return;
    }
}

// 修改表  修改名字和年龄 以id作为条件
void MainWindow::on_pushButton_8_clicked()
{
    QSqlQuery db_query(db);
    if (ui->lineEdit_id->text().isEmpty())
    {
        QMessageBox::warning(NULL,"id为空"," id是条件，不能为空！！！",QMessageBox::Close);
        return;
    }

    if (!ui->lineEdit_name->text().isEmpty() && !ui->lineEdit_age->text().isEmpty())
    {
        if (db_query.exec(QString("UPDATE student SET name='%1', age=%2 WHERE id=%3").arg(ui->lineEdit_name->text()).arg(ui->lineEdit_age->text()).arg(ui->lineEdit_id->text()))){
            QMessageBox::information(NULL,"修改表","修改name,age成功+命令："+db_query.lastQuery(),QMessageBox::Close);
        }
        else{
            QMessageBox::critical(NULL,"修改表","修改name,age失败:" +db_query.lastError().text() ,QMessageBox::Close);
            return;
        }
    }
    else if (!ui->lineEdit_name->text().isEmpty() && ui->lineEdit_age->text().isEmpty())
    {
        if (db_query.exec(QString("UPDATE student SET name='%1' WHERE id=%2").arg(ui->lineEdit_name->text()).arg(ui->lineEdit_id->text()))){
            QMessageBox::information(NULL,"修改表","修改name成功+命令："+db_query.lastQuery(),QMessageBox::Close);
        }
        else{
            QMessageBox::critical(NULL,"修改表","修改name失败:" +db_query.lastError().text() ,QMessageBox::Close);
            return;
        }
    }
    else if (ui->lineEdit_name->text().isEmpty() && !ui->lineEdit_age->text().isEmpty())
    {
        if (db_query.exec(QString("UPDATE student SET age=%1 WHERE id=%2").arg(ui->lineEdit_age->text()).arg(ui->lineEdit_id->text()))){
            QMessageBox::information(NULL,"修改表","修改name成功+命令："+db_query.lastQuery(),QMessageBox::Close);
        }
        else{
            QMessageBox::critical(NULL,"修改表","修改name失败:" +db_query.lastError().text() ,QMessageBox::Close);
            return;
        }
    }
    else
    {
            QMessageBox::critical(NULL,"修改表格","修改name,age 失败" + db_query.lastError().text() ,QMessageBox::Close);
            return;
    }
}

// 表结构
void MainWindow::on_pushButton_9_clicked()
{
   QSqlQuery db_query(db);
   QStringList tables = db.tables(); // 获取所有表格的名称
   for (int i = 0; i < tables.size(); i++) {
       ui->textBrowser_2->append(QString("表名: %1").arg(tables.at(i)));
   }

    // 循环遍历结果集并打印表结构信息
   ui->textBrowser_2->append(QString("\r\n表的结构如下："));
   db_query.exec("SELECT SQLITE_VERSION()");
   while (db_query.next()) {
       QString version = db_query.value(0).toString();
       ui->textBrowser_2->append(QString("SQLITE version: %1").arg(version));
   }
   if (tables.contains("student")) {
       db_query.exec("PRAGMA table_info(student)");
       while (db_query.next()) {
           QString index = db_query.value(0).toString();
           QString name = db_query.value(1).toString();
           QString type = db_query.value(2).toString();
           qDebug() <<" index = "<<index<<  " Column Name:" << name << ", Type:" << type;
           ui->textBrowser_2->append(QString("Column Name: %1 , Type:%2").arg(name).arg(type));
       }
   }
}
// 表格新增列
void MainWindow::on_pushButton_10_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("ALTER TABLE student ADD height INTEGER")){
        QMessageBox::information(NULL,"表格新增列","新增列成功+命令："+db_query.lastQuery(),QMessageBox::Close);
    }
    else{
        QMessageBox::information(NULL,"表格新增列","新增列失败:" +db_query.lastError().text() ,QMessageBox::Close);
        return;
    }
}

//单击表格单元格
void MainWindow::on_tableWidget_cellClicked(int row, int column)
{
   qDebug()<<" row = "<<row<<" column = "<<column<<" rowCount = "<<ui->tableWidget->rowCount();
   if (ui->tableWidget->item(row,column) == nullptr)
    {
        QMessageBox::warning(NULL,"表格","当前单元格没有数据项！！！",QMessageBox::Close);
        return;
    }

    if (column == 0)
        ui->lineEdit_id->setText(ui->tableWidget->item(row,column)->text());
    else if(column == 1)
        ui->lineEdit_name->setText(ui->tableWidget->item(row,column)->text());
    else if(column == 2)
        ui->lineEdit_age->setText(ui->tableWidget->item(row,column)->text());
    else if(column == 3)
        ui->lineEdit_qq->setText(ui->tableWidget->item(row,column)->text());
    else if(column == 4)
        ui->lineEdit_phone->setText(ui->tableWidget->item(row,column)->text());
    else
        return;
}

// 清空输入框
void MainWindow::on_pushButton_11_clicked()
{
    ui->lineEdit_id->clear();
    ui->lineEdit_age->clear();
    ui->lineEdit_name->clear();
    ui->lineEdit_qq->clear();
    ui->lineEdit_phone->clear();
}

// 计算数据库表格行数
void MainWindow::on_pushButton_12_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT count(*) FROM student"))
    {
        while (db_query.next()) {
            QString rowCount = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery()+";    表格行数 = "+rowCount);
        }
    }
}
// 计算表格最大值
void MainWindow::on_pushButton_13_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT max(id) FROM student"))
    {
        while (db_query.next()) {
            QString max = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   id列的最大值是 = "+max);
        }
    }
}

// 计算表格最小值
void MainWindow::on_pushButton_14_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT min(id) FROM student"))
    {
        while (db_query.next()) {
            QString min = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   id列的最小值是 = "+min);
        }
    }
}
// 计算表格某列平均值
void MainWindow::on_pushButton_15_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT avg(age) FROM student"))
    {
        while (db_query.next()) {
            QString avg = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   age列的平均值是 = "+avg);
        }
    }
}
// 计算表格某列总和
void MainWindow::on_pushButton_16_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT sum(age) FROM student"))
    {
        while (db_query.next()) {
            QString sum = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   age列的求和是 = "+sum);
        }
    }
}
// 计算一个随机数
void MainWindow::on_pushButton_18_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT random() AS random"))
    {
        while (db_query.next()) {
            QString random = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   random = "+random);
        }
    }
}
// 计算绝对值
void MainWindow::on_pushButton_17_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT abs(5),abs(-5),abs(NULL),abs('ABC')"))
    {
        while (db_query.next()) {
            QString abs1 = db_query.value(0).toString();
            QString abs2 = db_query.value(1).toString();
            QString abs3 = QString::number(db_query.value(2).toInt());
            QString abs4 = QString::number(db_query.value(3).toDouble());
            ui->textBrowser_3->append(db_query.lastQuery() +";   abs1 = "+abs1 +", adb2 = "+abs2+", abs3 = "+abs3+" ,abs4 = "+abs4);
        }
    }
}
// 字符串转化大写
void MainWindow::on_pushButton_20_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT upper('myname')"))
    {
        while (db_query.next()) {
            QString upper = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   upper(myname) = "+upper);
        }
    }
}
// 字符串转化小写
void MainWindow::on_pushButton_19_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT lower('MYNAME')"))
    {
        while (db_query.next()) {
            QString upper = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   lower(MYNAME) = "+upper);
        }
    }
}

// 计算字符串长度
void MainWindow::on_pushButton_21_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT name, length(name) FROM student"))
    {
        while (db_query.next()) {
            QString name = db_query.value(0).toString();
            quint32 len = db_query.value(1).toUInt();
            ui->textBrowser_3->append(db_query.lastQuery() +";   name = "+name+" len = "+QString::number(len));
        }
    }
}

// date 当前日期
void MainWindow::on_pushButton_24_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT date('now')"))
    {
        while (db_query.next()) {
            QString date = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   date = "+date);
        }
    }
    if (db_query.exec("SELECT date()"))
    {
        while (db_query.next()) {
            QString date = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   date = "+date);
        }
    }
    // 计算当前月份的最后一天：
    if (db_query.exec("SELECT date('now','start of month','+1 month','-1 day')"))
    {
        while (db_query.next()) {
            QString date = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   date = "+date);
        }
    }
    // 给定 UNIX 时间戳 1092941466 的日期和时间：
    if (db_query.exec("SELECT datetime()"))
    {
        while (db_query.next()) {
            QString datetime = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   datetime = "+datetime);
        }
    }
    if (db_query.exec("SELECT datetime(1092941466, 'unixepoch')"))
    {
        while (db_query.next()) {
            QString datetime = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   datetime = "+datetime);
        }
    }
    // 计算给定 UNIX 时间戳 1092941466 相对本地时区的日期和时间：
    if (db_query.exec("SELECT datetime(1092941466, 'unixepoch', 'localtime')"))
    {
        while (db_query.next()) {
            QString datetime = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   datetime = "+datetime);
        }
    }
    // 一周中的第几天，0-6 (0 is Sunday)
    if (db_query.exec("SELECT strftime('%w','now')"))
    {
        while (db_query.next()) {
            QString strftime = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   strftime = "+strftime + "【 '%w' 一周中的第几天，0-6 (0 is Sunday)】");
        }
    }
    if (db_query.exec("SELECT strftime('%s', 'now')"))
    {
        while (db_query.next()) {
            QString strftime = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   strftime = "+strftime + "【 \"%s\"	从 1970-01-01 算起的秒数】");
        }
    }
    if (db_query.exec("SELECT strftime('%Y-%m-%d %H', 'now')"))
    {
        while (db_query.next()) {
            QString strftime = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   strftime = "+strftime);
        }
    }

    // 计算从 2004 年某一特定时刻以来的秒数：
    if (db_query.exec("SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56')"))
    {
        while (db_query.next()) {
            QString strftime = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   strftime = "+strftime + "【 \"%s\"	从 1970-01-01 算起的秒数】");
        }
    }
    if (db_query.exec("SELECT strftime('%Y年%m月%d日 %H时:%M分:%S秒  周%w')"))
    {
        while (db_query.next()) {
            QString strftime = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   strftime = "+strftime +"\r\n");
        }
    }

    // 计算当年 10 月的第一个星期二的日期：
    if (db_query.exec("SELECT date('now','start of year','+9 months','weekday 2')"))
    {
        while (db_query.next()) {
            QString date = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   date = "+date);
        }
    }

    // 在 UTC 与本地时间值之间进行转换，当格式化日期时，使用 utc 或 localtime 修饰符，如下所示：
    if (db_query.exec("SELECT time('12:00', 'localtime')"))
    {
        while (db_query.next()) {
            QString time = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   time = "+time);
        }
    }
    // 在 UTC 与本地时间值之间进行转换，当格式化日期时，使用 utc 或 localtime 修饰符，如下所示：
    if (db_query.exec("SELECT time('12:00', 'utc')"))
    {
        while (db_query.next()) {
            QString time = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   time = "+time);
        }
    }
}

// 百分号 （%）下划线 （_） 百分号（%）代表零个、一个或多个数字或字符。下划线（_）代表一个单一的数字或字符。这些符号可以被组合使用。
void MainWindow::on_pushButton_22_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec(QString("SELECT *FROM student WHERE name LIKE '%1'").arg(ui->lineEdit_search->text())))
    {
        while (db_query.next()) {
            QString id = db_query.value(0).toString();
            QString name = db_query.value(1).toString();
            QString age = db_query.value(2).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   id = "+id +" name = "+name+" age = "+age);
        }
    }
    else{
        QMessageBox::information(NULL,"查询","查询失败:" +db_query.lastError().text() ,QMessageBox::Close);
        return;
    }
}

void MainWindow::on_pushButton_27_clicked()
{
    ui->textBrowser_3->clear();
}
// 星号（*）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符。这些符号可以被组合使用。
void MainWindow::on_pushButton_25_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec(QString("SELECT *FROM student WHERE name GLOB '%1'").arg(ui->lineEdit_search->text())))
    {
        while (db_query.next()) {
            QString id = db_query.value(0).toString();
            QString name = db_query.value(1).toString();
            QString age = db_query.value(2).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   id = "+id +" name = "+name+" age = "+age);
        }
    }
    else{
        QMessageBox::information(NULL,"查询","查询失败:" +db_query.lastError().text() ,QMessageBox::Close);
        return;
    }
}
// SQLite 的 LIMIT 子句用于限制由 SELECT 语句返回的数据数量。
void MainWindow::on_pushButton_23_clicked()
{
    QSqlQuery db_query(db);
    // Limit 提取的行数 Offet 从第一行开始跳过多少行
    if (db_query.exec(QString("SELECT * FROM student LIMIT %1 OFFSET %2").arg(ui->lineEdit_search->text().split('/').at(0)).arg(ui->lineEdit_search->text().split('/').at(1))))
    {
        while (db_query.next()) {
            QString id = db_query.value(0).toString();
            QString name = db_query.value(1).toString();
            QString age = db_query.value(2).toString();
            QString qq = db_query.value(3).toString();
            QString phone = db_query.value(4).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   id = "+id +" name = "+name+" age = "+age +" qq = "+qq +" phone = "+phone);
        }
    }
    else{
        QMessageBox::information(NULL,"查询","查询失败:" +db_query.lastError().text() ,QMessageBox::Close);
        return;
    }
}
// 表格排序
void MainWindow::on_pushButton_26_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec(QString("SELECT * FROM student ORDER BY %1").arg(ui->lineEdit_search->text())))
    {
        while (db_query.next()) {
            QString id = db_query.value(0).toString();
            QString name = db_query.value(1).toString();
            QString age = db_query.value(2).toString();
            QString qq = db_query.value(3).toString();
            quint64 phone = db_query.value(4).toULongLong();
            ui->textBrowser_3->append(db_query.lastQuery() +";   id = "+id +" name = "+name+" age = "+age+" qq = "+qq+" phone = "+phone);
        }
    }
    else{
        QMessageBox::information(NULL,"查询","查询失败:" +db_query.lastError().text() ,QMessageBox::Close);
        return;
    }
}
// 在 SELECT 语句中，GROUP BY 子句必须放在 WHERE 子句之后，放在 ORDER BY 子句之前。
void MainWindow::on_pushButton_30_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec(QString("SELECT name, SUM(age) FROM student GROUP BY name ORDER BY name DESC")))
    {
        while (db_query.next()) {
            QString name = db_query.value(0).toString();
            QString sum = db_query.value(1).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   name = "+name +" sum = "+sum);
        }
    }
    else{
        QMessageBox::information(NULL,"查询","查询失败:" +db_query.lastError().text() ,QMessageBox::Close);
        return;
    }
}
// WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。
void MainWindow::on_pushButton_29_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec(QString("SELECT * FROM student GROUP BY name HAVING length(name) < 2")))
    {
        while (db_query.next()) {
            QString name = db_query.value(1).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   name = "+name);
        }
    }
    else{
        QMessageBox::information(NULL,"查询","查询失败:" +db_query.lastError().text() ,QMessageBox::Close);
        return;
    }

    if (db_query.exec(QString("SELECT * FROM student GROUP BY name HAVING length(name) >= 2")))
    {
        while (db_query.next()) {
            QString name = db_query.value(1).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   name = "+name);
        }
    }
    else{
        QMessageBox::information(NULL,"查询","查询失败:" +db_query.lastError().text() ,QMessageBox::Close);
        return;
    }
}
// 在一个表中有多个重复的记录。当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。
void MainWindow::on_pushButton_33_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec(QString("SELECT DISTINCT name FROM student")))  // 查询没有重复的name
    {
        while (db_query.next()) {
            QString name = db_query.value(0).toString();
            ui->textBrowser_3->append(db_query.lastQuery() +";   name = "+name);
        }
    }
    else{
        QMessageBox::information(NULL,"查询","查询失败:" +db_query.lastError().text() ,QMessageBox::Close);
        return;
    }
}

// 上一页
void MainWindow::on_pb_back_2_clicked()
{
    ui->stackedWidget->setCurrentIndex(1);
}
// 下一页
void MainWindow::on_pb_next_2_clicked()
{
    ui->stackedWidget->setCurrentIndex(2);
}
//=======老师表创建======
void MainWindow::on_pushButton_28_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("CREATE TABLE teacher (id_t INTEGER,name_t TEXT,age_t INTEGER)")){
        QMessageBox::information(NULL,"创建表","创建成功:"+db_query.lastQuery(),QMessageBox::Close);
    }
    else{
        QMessageBox::critical(NULL,"创建表","表创建失败：" + db_query.lastError().text(),QMessageBox::Close);
    }


    if (db_query.exec(QString("INSERT INTO teacher (id_t,name_t,age_t) VALUES (%1,'%2',%3)").arg(ui->lineEdit_id->text()).arg(ui->lineEdit_name->text()).arg(ui->lineEdit_age->text()))){
        QMessageBox::information(NULL,"插入表","插入成功:"+db_query.lastQuery(),QMessageBox::Close);
    }
    else{
        QMessageBox::critical(NULL,"插入表","插入失败:" +db_query.lastError().text() ,QMessageBox::Close);
        return;
    }

    if (db_query.exec("SELECT id_t,name_t,age_t FROM teacher ORDER BY id_t ASC"))
    {
        QMessageBox::information(NULL,"查询表","查询成功:"+db_query.lastQuery(),QMessageBox::Close);

        int row = 9;
        while (db_query.next()) {
            int id = db_query.value(0).toInt();
            QString name = db_query.value(1).toString();
            int age = db_query.value(2).toInt();
            ui->tableWidget->setItem(row,0,new QTableWidgetItem(QString::number(id)));
            ui->tableWidget->setItem(row,1,new QTableWidgetItem(name));
            ui->tableWidget->setItem(row,2,new QTableWidgetItem(QString::number(age)));
            row += 1;   //行数动态增加
        }
    }
    else {
        QMessageBox::critical(NULL,"查询表","查询失败:" +db_query.lastError().text() ,QMessageBox::Close);
        return;
    }
}
// 交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 列，则结果表有 x*y 列。
// 由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。
void MainWindow::on_pushButton_31_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT id_t, name, age_t FROM student CROSS JOIN teacher")){
        QMessageBox::information(NULL,"交叉连接","交叉连接成功:"+db_query.lastQuery(),QMessageBox::Close);

        while (db_query.next()) {
            int id = db_query.value(0).toUInt();
            QString name = db_query.value(1).toString();
            int age = db_query.value(2).toUInt();
            ui->textBrowser_4->append(db_query.lastQuery() +";   id_t = "+QString::number(id) +" name = " +name +" age_t = "+QString::number(age));
        }
    }
    else{
        QMessageBox::critical(NULL,"交叉连接","交叉连接失败：" + db_query.lastError().text(),QMessageBox::Close);
    }
}

// 内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。
void MainWindow::on_pushButton_32_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT id_t, name, age_t FROM student INNER JOIN  teacher ON student.id = teacher.id_t")){
        QMessageBox::information(NULL,"内连接","内连接成功:"+db_query.lastQuery(),QMessageBox::Close);

        while (db_query.next()) {
            int id = db_query.value(0).toUInt();
            QString name = db_query.value(1).toString();
            int age = db_query.value(2).toUInt();
            ui->textBrowser_4->append(db_query.lastQuery() +";   id_t = "+QString::number(id) +" name = " +name +" age_t = "+QString::number(age));
        }
    }
    else{
        QMessageBox::critical(NULL,"内连接","内连接失败：" + db_query.lastError().text(),QMessageBox::Close);
    }
}
// 外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）。
void MainWindow::on_pushButton_34_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT id_t, name, age_t FROM student LEFT OUTER JOIN  teacher ON student.id = teacher.id_t")){
        QMessageBox::information(NULL,"外连接","外连接成功:"+db_query.lastQuery(),QMessageBox::Close);

        while (db_query.next()) {
            int id_t = db_query.value(0).toUInt();
            QString name = db_query.value(1).toString();
            int age = db_query.value(2).toUInt();
            ui->textBrowser_4->append(db_query.lastQuery() +";   id_t = "+QString::number(id_t) +" name = " +name +" age_t = "+QString::number(age));
        }
    }
    else{
        QMessageBox::critical(NULL,"外连接","外连接失败：" + db_query.lastError().text(),QMessageBox::Close);
    }
}
// SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行。
// UNION ALL 运算符用于结合两个 SELECT 语句的结果，包括重复行。
void MainWindow::on_pushButton_35_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT id_t, name, age_t FROM student INNER JOIN teacher \
                      ON student.id = teacher.id_t \
                 UNION \
                   SELECT id_t, name, age_t FROM student LEFT OUTER JOIN teacher \
                      ON student.id = teacher.id_t")){
        QMessageBox::information(NULL,"UNION 子句","UNION 子句成功:"+db_query.lastQuery(),QMessageBox::Close);

        while (db_query.next()) {
            int id_t = db_query.value(0).toUInt();
            QString name = db_query.value(1).toString();
            int age = db_query.value(2).toUInt();
            ui->textBrowser_4->append(db_query.lastQuery() +";   id_t = "+QString::number(id_t) +" name = " +name +" age_t = "+QString::number(age));
        }
    }
    else{
        QMessageBox::critical(NULL,"UNION 子句","UNION 子句失败：" + db_query.lastError().text(),QMessageBox::Close);
    }

    ui->textBrowser_4->append("\n");

    if (db_query.exec("SELECT id_t, name, age_t FROM student INNER JOIN teacher \
        ON student.id = teacher.id_t \
        UNION ALL\
        SELECT id_t, name, age_t FROM student LEFT OUTER JOIN teacher \
        ON student.id = teacher.id_t"))
    {
        QMessageBox::information(NULL,"UNION 子句","UNION 子句成功:"+db_query.lastQuery(),QMessageBox::Close);

        while (db_query.next()) {
            int id_t = db_query.value(0).toUInt();
            QString name = db_query.value(1).toString();
            int age = db_query.value(2).toUInt();
            ui->textBrowser_4->append(db_query.lastQuery() +";   id_t = "+QString::number(id_t) +" name = " +name +" age_t = "+QString::number(age));
        }
    }
    else{
    QMessageBox::critical(NULL,"UNION 子句","UNION 子句失败：" + db_query.lastError().text(),QMessageBox::Close);
    }
}
// SQLite 触发器（Trigger）是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。
void MainWindow::on_pushButton_36_clicked()
{
      QSqlQuery db_query(db);
      if (db_query.exec("CREATE TABLE AUDIT(EMP_ID INTEGER ,ENTRY_DATE TEXT)")){
          QMessageBox::information(NULL,"Trigger","Trigger成功:"+db_query.lastQuery(),QMessageBox::Close);
      }
      else{
          QMessageBox::critical(NULL,"Trigger","Trigger失败：" + db_query.lastError().text(),QMessageBox::Close);
      }

      if (db_query.exec("CREATE TRIGGER audit_log AFTER INSERT\
                        ON student\
                        BEGIN\
                           INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));\
                        END")){
          QMessageBox::information(NULL,"Trigger","Trigger成功:"+db_query.lastQuery(),QMessageBox::Close);

          while (db_query.next()) {
              int id_t = db_query.value(0).toUInt();
              QString name = db_query.value(1).toString();
              int age = db_query.value(2).toUInt();
              ui->textBrowser_4->append(db_query.lastQuery() +";   id_t = "+QString::number(id_t) +" name = " +name +" age_t = "+QString::number(age));
          }
      }
      else{
          QMessageBox::critical(NULL,"Trigger","Trigger失败：" + db_query.lastError().text(),QMessageBox::Close);
      }

    if (db_query.exec("SELECT EMP_ID, ENTRY_DATE FROM AUDIT")){
        QMessageBox::information(NULL,"Trigger","Trigger成功:"+db_query.lastQuery(),QMessageBox::Close);

            while (db_query.next()) {
            int id_t = db_query.value(0).toUInt();
            QString name = db_query.value(1).toString();
            ui->textBrowser_4->append(db_query.lastQuery() +";   EMP_ID = "+QString::number(id_t) +" ENTRY_DATE = " +name);
        }
    }
    else{
        QMessageBox::critical(NULL,"Trigger","Trigger失败：" + db_query.lastError().text(),QMessageBox::Close);
    }
}
//  视图（View）只不过是通过相关的名称存储在数据库中的一个 SQLite 语句。视图（View）实际上是一个以预定义的 SQLite 查询形式存在的表的组合。
void MainWindow::on_pushButton_37_clicked()
{
        QSqlQuery db_query(db);
        if (db_query.exec("CREATE VIEW student_VIEW AS SELECT id, name, age FROM  student")){
            QMessageBox::information(NULL,"视图（View）","视图（View）成功:"+db_query.lastQuery(),QMessageBox::Close);
        }
        else{
            QMessageBox::critical(NULL,"视图（View）","视图（View）失败：" + db_query.lastError().text(),QMessageBox::Close);
        }

        if (db_query.exec("SELECT * FROM student_VIEW")){
                QMessageBox::information(NULL,"视图（View）","视图（View）成功:"+db_query.lastQuery(),QMessageBox::Close);

                while (db_query.next()) {
                int id = db_query.value(0).toUInt();
                QString name = db_query.value(1).toString();
                int age = db_query.value(2).toUInt();
                ui->textBrowser_4->append(db_query.lastQuery() +";   id = "+QString::number(id) +" name = " +name +" age =  "+QString::number(age));
            }
        }
        else{
              QMessageBox::critical(NULL,"视图（View）","视图（View）失败：" + db_query.lastError().text(),QMessageBox::Close);
        }

        if (db_query.exec("DROP VIEW student_VIEW")){
            QMessageBox::information(NULL,"视图（View）","视图（View）成功:"+db_query.lastQuery(),QMessageBox::Close);
        }
        else{
            QMessageBox::critical(NULL,"视图（View）","视图（View）失败：" + db_query.lastError().text(),QMessageBox::Close);
        }

}
// 事务（Transaction）是一个对数据库执行工作单元。事务（Transaction）是以逻辑顺序完成的工作单位或序列，可以是由用户手动操作完成，也可以是由某种数据库程序自动完成。
void MainWindow::on_pushButton_38_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("BEGIN")){     // 开始事务
    QMessageBox::information(NULL,"事务（Transaction）","事务（Transaction）成功:" + db_query.lastQuery(),QMessageBox::Close);
    }
    else{
    QMessageBox::critical(NULL,"事务（Transaction）","事务（Transaction）失败：" + db_query.lastError().text(),QMessageBox::Close);
    }
    if (db_query.exec(QString("DELETE FROM student WHERE age = %1").arg(ui->lineEdit->text().toUInt()))){
        QMessageBox::information(NULL,"事务（Transaction）","事务（Transaction）成功:" + db_query.lastQuery(),QMessageBox::Close);
    }
    else{
        QMessageBox::critical(NULL,"事务（Transaction）","事务（Transaction）失败：" + db_query.lastError().text(),QMessageBox::Close);
    }

    if (db_query.exec("SELECT age FROM student")){
            QMessageBox::information(NULL,"语句","命令:"+db_query.lastQuery(),QMessageBox::Close);

            while (db_query.next()) {
            int age = db_query.value(0).toUInt();
            ui->textBrowser_4->append(db_query.lastQuery() +";  age =  "+QString::number(age));
        }
    }

    if (db_query.exec("ROLLBACK")){  // 撤销以上操作
    QMessageBox::information(NULL,"事务（Transaction）","事务（Transaction）成功:" + db_query.lastQuery(),QMessageBox::Close);
    }
    else{
    QMessageBox::critical(NULL,"事务（Transaction）","事务（Transaction）失败：" + db_query.lastError().text(),QMessageBox::Close);
    }

    //==========================================保存更改=====================================
    if (db_query.exec("BEGIN")){        // 开始事务
        QMessageBox::information(NULL,"事务（Transaction）","事务（Transaction）成功:" + db_query.lastQuery(),QMessageBox::Close);
    }
    else{
        QMessageBox::critical(NULL,"事务（Transaction）","事务（Transaction）失败：" + db_query.lastError().text(),QMessageBox::Close);
    }

    if (db_query.exec(QString("DELETE FROM student WHERE age = %1").arg(ui->lineEdit->text().toUInt()))){
        QMessageBox::information(NULL,"事务（Transaction）","事务（Transaction）成功:" + db_query.lastQuery(),QMessageBox::Close);
    }
    else{
        QMessageBox::critical(NULL,"事务（Transaction）","事务（Transaction）失败：" + db_query.lastError().text(),QMessageBox::Close);
    }


    if (db_query.exec("COMMIT")){ // 提交事务
        QMessageBox::information(NULL,"事务（Transaction）","事务（Transaction）成功:"+db_query.lastQuery(),QMessageBox::Close);
    }
    else{
        QMessageBox::critical(NULL,"事务（Transaction）","事务（Transaction）失败：" + db_query.lastError().text(),QMessageBox::Close);
    }

    if (db_query.exec("SELECT age FROM student")){
            QMessageBox::information(NULL,"语句","命令:"+db_query.lastQuery(),QMessageBox::Close);

            while (db_query.next()) {
            int age = db_query.value(0).toUInt();
            ui->textBrowser_4->append(db_query.lastQuery() +";  age =  "+QString::number(age));
        }
    }
}

// 删除第三个文本框
void MainWindow::on_pushButton_39_clicked()
{
    ui->textBrowser_4->clear();
}
// 子查询或内部查询或嵌套查询是在另一个 SQLite 查询内嵌入在 WHERE 子句中的查询。
void MainWindow::on_pushButton_40_clicked()
{
    QSqlQuery db_query(db);
    if (db_query.exec("SELECT *FROM student WHERE id IN (SELECT id FROM student WHERE id  > 1) ")){
            QMessageBox::information(NULL,"子查询","子查询成功:"+db_query.lastQuery(),QMessageBox::Close);

            while (db_query.next()) {
            int id = db_query.value(0).toUInt();
            QString name = db_query.value(1).toString();
            int age = db_query.value(2).toUInt();
            ui->textBrowser_4->append(db_query.lastQuery() +";   id = "+QString::number(id) +" name = " +name +" age =  "+QString::number(age));
        }
    }
    else{
          QMessageBox::critical(NULL,"子查询","子查询失败：" + db_query.lastError().text(),QMessageBox::Close);
    }

    if (db_query.exec("UPDATE student SET id = id * 5 WHERE age IN (SELECT age FROM student WHERE age >= 1 )")){
            QMessageBox::information(NULL,"子查询","子查询成功:"+db_query.lastQuery(),QMessageBox::Close);

            while (db_query.next()) {
            int id = db_query.value(0).toUInt();
            QString name = db_query.value(1).toString();
            int age = db_query.value(2).toUInt();
            ui->textBrowser_4->append(db_query.lastQuery() +";   id = "+QString::number(id) +" name = " +name +" age =  "+QString::number(age));
        }
    }
    else{
          QMessageBox::critical(NULL,"子查询","子查询失败：" + db_query.lastError().text(),QMessageBox::Close);
    }

    if (db_query.exec(" DELETE FROM student WHERE age IN (SELECT age FROM student WHERE age > 60 )")){
            QMessageBox::information(NULL,"子查询","子查询成功:"+db_query.lastQuery(),QMessageBox::Close);

            while (db_query.next()) {
            int id = db_query.value(0).toUInt();
            QString name = db_query.value(1).toString();
            int age = db_query.value(2).toUInt();
            ui->textBrowser_4->append(db_query.lastQuery() +";   id = "+QString::number(id) +" name = " +name +" age =  "+QString::number(age));
        }
    }
    else{
          QMessageBox::critical(NULL,"子查询","子查询失败：" + db_query.lastError().text(),QMessageBox::Close);
    }
}
// 来自 EXPLAIN 和 EXPLAIN QUERY PLAN 的输出只用于交互式分析和排除故障。
void MainWindow::on_pushButton_41_clicked()
{
        QSqlQuery db_query(db);
        if (db_query.exec("EXPLAIN SELECT *  FROM student  WHERE age >= 1")){
                QMessageBox::information(NULL,"子查询","子查询成功:"+db_query.lastQuery(),QMessageBox::Close);

                while (db_query.next()) {
                QString detail = db_query.value(0).toString(); // 获取查询计划的详细信息
                ui->textBrowser_4->append(db_query.lastQuery() +"; detail =  "+detail);
            }
        }
        else{
              QMessageBox::critical(NULL,"子查询","子查询失败：" + db_query.lastError().text(),QMessageBox::Close);
        }

        ui->textBrowser_4->append("\n");

        if (db_query.exec("EXPLAIN QUERY PLAN SELECT *  FROM student  WHERE age >= 1")){
                QMessageBox::information(NULL,"子查询","子查询成功:"+db_query.lastQuery(),QMessageBox::Close);

                while (db_query.next()) {
                QString detail = db_query.value(0).toString(); // 获取查询计划的详细信息
                ui->textBrowser_4->append(db_query.lastQuery() +"; detail =  "+detail);
            }
        }
        else{
              QMessageBox::critical(NULL,"子查询","子查询失败：" + db_query.lastError().text(),QMessageBox::Close);
        }
}
